-- 采集任务表
drop table MDA_ETL_EXTRACTJOB purge;
create table MDA_ETL_EXTRACTJOB
(
  ID             VARCHAR2(50) not null,
  STATUS         VARCHAR2(20) not null,
  CREATETIME     DATE not null,
  EXECUTETIME    DATE,
  RESPONSETIME   DATE,
  VARIABLES      CLOB,
  DATATYPE       VARCHAR2(200) not null,
  RULENAME   VARCHAR2(200) not null,
  OUTPUTRESOURCE VARCHAR2(200),
  EXCEPTION      CLOB,
  RETRIES        INTEGER default 0 not null,
  RESPONSSTATUS  VARCHAR2(20)
);
comment on table MDA_ETL_EXTRACTJOB
  is 'ETL采集任务表';
comment on column MDA_ETL_EXTRACTJOB.ID
  is '主键';
comment on column MDA_ETL_EXTRACTJOB.STATUS
  is '状态(waiting,error,succeed,finish)';
comment on column MDA_ETL_EXTRACTJOB.CREATETIME
  is '创建时间';
comment on column MDA_ETL_EXTRACTJOB.EXECUTETIME
  is '执行时间';
comment on column MDA_ETL_EXTRACTJOB.RESPONSETIME
  is '响应时间';
comment on column MDA_ETL_EXTRACTJOB.VARIABLES
  is '变量';
comment on column MDA_ETL_EXTRACTJOB.DATATYPE
  is '数据类型';
comment on column MDA_ETL_EXTRACTJOB.RULENAME
  is '规则实例';
comment on column MDA_ETL_EXTRACTJOB.OUTPUTRESOURCE
  is '输出资源';
comment on column MDA_ETL_EXTRACTJOB.EXCEPTION
  is '异常';
comment on column MDA_ETL_EXTRACTJOB.RETRIES
  is '重试次数';
comment on column MDA_ETL_EXTRACTJOB.RESPONSSTATUS
  is '响应状态';
alter table MDA_ETL_EXTRACTJOB
  add primary key (ID)
  using index;
create index IDX_MDA_EXTRACT_SRD on MDA_ETL_EXTRACTJOB (STATUS,RULENAME,DATATYPE);
create unique index IDX_MDA_EXTRACT_ORD on MDA_ETL_EXTRACTJOB (OUTPUTRESOURCE, RULENAME, DATATYPE);

--
drop table MDA_ETL_LOADJOB purge;
create table MDA_ETL_LOADJOB
(
  ID            VARCHAR2(50) not null,
  STATUS        VARCHAR2(20) not null,
  CREATETIME    DATE not null,
  EXECUTETIME   DATE,
  DATATYPE      VARCHAR2(50) not null,
  RULENAME  VARCHAR2(200) not null,
  INPUTRESOURCE VARCHAR2(200) not null,
  RETRIES        INTEGER default 0 not null,
  EXCEPTION     CLOB
);
comment on table MDA_ETL_LOADJOB
  is 'ETL装载任务表';
comment on column MDA_ETL_LOADJOB.ID
  is '主键';
comment on column MDA_ETL_LOADJOB.STATUS
  is '状态';
comment on column MDA_ETL_LOADJOB.CREATETIME
  is '创建时间';
comment on column MDA_ETL_LOADJOB.EXECUTETIME
  is '执行时间';
comment on column MDA_ETL_LOADJOB.DATATYPE
  is '数据类型';
comment on column MDA_ETL_LOADJOB.RULENAME
  is '规则实例';
comment on column MDA_ETL_LOADJOB.INPUTRESOURCE
  is '输入之源';
comment on column MDA_ETL_LOADJOB.EXCEPTION
  is '异常';
alter table MDA_ETL_LOADJOB
  add primary key (ID)
  using index;
create index IDX_MDA_LOAD_SRD on MDA_ETL_LOADJOB (STATUS,RULENAME,DATATYPE);

create unique index IDX_MDA_LOAD_DRR on MDA_ETL_LOADJOB (INPUTRESOURCE, RULENAME, DATATYPE);
  
-- DML日志表
drop table MDA_TableDMLLog purge;
create table MDA_TableDMLLog
(
  guid        VARCHAR2(32) not null,
  tablename   VARCHAR2(30),
  keyvalues   VARCHAR2(200),
  occurtime   TIMESTAMP(6),
  operation   VARCHAR2(20),
  attrs		  VARCHAR2(4000),
  VERSION    Integer
);
comment on table MDA_TableDMLLog
  is 'DML日志表';
comment on column MDA_TableDMLLog.guid
  is '主键';
comment on column MDA_TableDMLLog.tablename
  is '表名';
comment on column MDA_TableDMLLog.keyvalues
  is '主键值';
comment on column MDA_TableDMLLog.occurtime
  is '发生时间';
comment on column MDA_TableDMLLog.operation
  is '操作';
comment on column MDA_TableDMLLog.attrs
  is '属性(删除时)';
  
  comment on column MDA_TableDMLLog.VERSION
  is '版本';
  
create unique index IDX_MDA_TABLEDMLLOG_LOGICKEY on MDA_TABLEDMLLOG (KEYVALUES, TABLENAME);
  
drop table MDA_TableDMLWatch purge;
  
-- DML监控配置
create table MDA_TableDMLWatch
(
  tablename   VARCHAR2(50) not null,
  primaryKey  VARCHAR2(20) not null,
  operation   VARCHAR2(200),
  recordFields   VARCHAR2(200)
);
comment on table MDA_TableDMLWatch
  is 'DML监控配置';
comment on column MDA_TableDMLWatch.tablename
  is '表名';
comment on column MDA_TableDMLWatch.primaryKey
  is '主键字段';
comment on column MDA_TableDMLWatch.operation
  is '监控的操作(UPSERT,DELETE)';
comment on column MDA_TableDMLWatch.recordFields
  is '删除时需记录的字段，多个字段逗号分开';
alter table MDA_TableDMLWatch add primary key (tablename) using index;


create or replace procedure genDMLLog(tableName varchar2, keyValues varchar2,operation varchar2,attrs varchar2) is
begin
  if operation = 'DELETE' then
     execute immediate 'delete from mda_TableDMLLog where tableName=:1 and keyValues=:2' using tableName, keyValues;
  end if;
  execute immediate 'merge into mda_TableDMLLog T1 using '
   || ' (select sys_guid() as guid,0 as VERSION,:1 as tableName,:2 as keyValues,:3 as operation,systimestamp as occurTime,:4 as attrs from dual) T2'
   || ' on (T1.tableName=T2.tableName and T1.keyValues=T2.keyValues)'
   || ' when matched then update set t1.occurTime=t2.occurTime,t1.attrs=t2.attrs,t1.operation=t2.operation'
   || ' when not matched then insert (guid,VERSION,tableName,keyValues,operation,occurTime,attrs)'
   || ' values (T2.guid,T2.VERSION,T2.tableName,T2.keyValues,T2.operation,T2.occurTime,T2.attrs)'
  using tableName, keyValues, operation,attrs;

end genDMLLog;

